﻿#region Librerias

using System;
using System.Data;
using System.Text;
using ESF.Data;
using ESF.General;
using ESF.General.Entity;
using ESF.General.Text;
using System.Collections.Generic;

#endregion


public static class AdoGeneral
{
	#region Referencias

	internal static DbReader R = new DbReader ("ErrorLogger");

	internal static DbWriter W = new DbWriter ("ErrorLogger");

	#endregion


	#region Inicializacion

	public static bool DoesTableExist (string pTableName)
    {
		IDbCommand cmd;


		cmd = R.CreateCommand (string.Format ("SELECT name FROM sqlite_master WHERE name='{0}'", pTableName));
      
		return R.HasRows (cmd);
    }

	#endregion


	#region Inicializacion

	public static void CreateTables ()
	{
		IDbCommand cmd;


		cmd = W.CreateCommand("CREATE TABLE IF NOT EXISTS Error (ID INTEGER PRIMARY KEY AUTOINCREMENT, code VARCHAR (500), stackTrace VARCHAR (4000), applicationName VARCHAR (500), machineName VARCHAR (500), machineAddress VARCHAR (500), remoteMachineAddress VARCHAR (500), userName VARCHAR (500), domainName VARCHAR (500), OSVersion VARCHAR (500), runtimeVersion VARCHAR (500), message VARCHAR (500), detailMessage VARCHAR (4000), errorLogDate TIMESTAMP, customUserName VARCHAR (200), productVersion VARCHAR (200), exceptionType VARCHAR (500), webProxyAddressPort VARCHAR (200))");
	
		W.Execute (cmd);

		cmd = W.CreateCommand ("CREATE INDEX IF NOT EXISTS [IX_applicationName] ON [Error]( [applicationName]  ASC )");

		W.Execute (cmd);

		cmd = W.CreateCommand("CREATE INDEX IF NOT EXISTS [IX_exceptionType] ON [Error]( [exceptionType]  ASC )");

		W.Execute(cmd);
	}

	#endregion


	#region Lectura

	public static List<BasicReference> ListApplicationName (ErrorReport pFilter)
	{
		IDbCommand cmd;


		cmd = R.CreateCommand("SELECT * FROM (SELECT applicationName Name, count (applicationName) Extra_I FROM Error WHERE 1 = 1 ");

		if (pFilter.filStartErrorLogDate != null)
		{
			cmd.CommandText += " AND errorLogDate >= @pStartErrorLogDate";
			R.AddInitialDateParameter(cmd, "@pStartErrorLogDate", pFilter.filStartErrorLogDate);
		}

		if (pFilter.filEndErrorLogDate != null)
		{
			cmd.CommandText += " AND errorLogDate <= @pEndErrorLogDate";
			R.AddFinalDateParameter(cmd, "@pEndErrorLogDate", pFilter.filEndErrorLogDate);
		}

		if (!string.IsNullOrEmpty(pFilter.UserName))
		{
			cmd.CommandText += " AND (customUserName LIKE @pUserLIKE OR userName LIKE @pUserLIKE)";
			R.AddParameter(cmd, "@pUserLIKE", pFilter.UserName, ComparisonTypes.LikeFull);
		}

		cmd.CommandText += " GROUP BY applicationName) ORDER BY Extra_I DESC";

		return R.GetListBasicReference (cmd);
	}


	private static List<BasicReference> ListGeneric (ErrorReport pFilter, string pColumnName)
	{
		IDbCommand cmd;


		cmd = R.CreateCommand(string.Format("SELECT * FROM (SELECT {0} Name, count ({0}) Extra_I FROM Error WHERE applicationName = @pApplicationName AND {0} IS NOT NULL ", pColumnName));

		R.AddParameter(cmd, "@pApplicationName", pFilter.ApplicationName);

		if (pFilter.filStartErrorLogDate != null)
		{
			cmd.CommandText += " AND errorLogDate >= @pStartErrorLogDate";
			R.AddInitialDateParameter(cmd, "@pStartErrorLogDate", pFilter.filStartErrorLogDate);
		}

		if (pFilter.filEndErrorLogDate != null)
		{
			cmd.CommandText += " AND errorLogDate <= @pEndErrorLogDate";
			R.AddFinalDateParameter(cmd, "@pEndErrorLogDate", pFilter.filEndErrorLogDate);
		}

		if (!string.IsNullOrEmpty(pFilter.UserName))
		{
			cmd.CommandText += " AND (customUserName LIKE @pUserLIKE OR userName LIKE @pUserLIKE)";
			R.AddParameter(cmd, "@pUserLIKE", pFilter.UserName, ComparisonTypes.LikeFull);
		}

		cmd.CommandText += string.Format (" GROUP by {0}) X ORDER BY X.Extra_I DESC", pColumnName);

		return R.GetListBasicReference (cmd);
	}


	public static List<BasicReference> ListExceptionType (ErrorReport pFilter)
	{
		return ListGeneric (pFilter, "exceptionType");
	}


	public static List<BasicReference> ListCode(ErrorReport pFilter)
	{
		return ListGeneric (pFilter, "code");
	}


	public static List<BasicReference> ListMessage(ErrorReport pFilter)
	{
		return ListGeneric (pFilter, "message");
	}


	public static List<BasicReference> ListDetailMessage(ErrorReport pFilter)
	{
		return ListGeneric (pFilter, "detailMessage");
	}



	public static BrowseResults<ErrorReport> GetErrors (ErrorReport pErrorReport, PaggingInfo pPagInfo)
	{
		StringBuilder whereCmd;
		
		IDbCommand cmd;
		
		string temp;

		string mainSQL;


		cmd = R.CreateCommand ();

		whereCmd = new StringBuilder ();
		
		if (!string.IsNullOrEmpty (pErrorReport.ApplicationName))
		{
			whereCmd.Append (" AND applicationName = @pApplicationName");
			R.AddParameter (cmd, "@pApplicationName", pErrorReport.ApplicationName);
		}


		if (!string.IsNullOrEmpty(pErrorReport.UserName))
		{
			whereCmd.Append(" AND (customUserName LIKE @pUserLIKE OR userName LIKE @pUserLIKE)");
			R.AddParameter(cmd, "@pUserLIKE", pErrorReport.UserName, ComparisonTypes.LikeFull);
		}
		
		if (!string.IsNullOrEmpty (pErrorReport.MachineName))
		{
			whereCmd.Append (" AND machineName LIKE @pMachineNameLIKE");
			R.AddParameter (cmd, "@pMachineNameLIKE", pErrorReport.MachineName, ComparisonTypes.LikeFull);
		}
		
		if (!string.IsNullOrEmpty (pErrorReport.MachineAddress))
		{
			whereCmd.Append (" AND machineAddress LIKE @pMachineAddressLIKE");
			R.AddParameter (cmd, "@pMachineAddressLIKE", pErrorReport.MachineAddress, ComparisonTypes.LikeFull);
		}

		if (!string.IsNullOrEmpty (pErrorReport.Code))
		{
			whereCmd.Append (" AND code = @pCode");
			R.AddParameter (cmd, "@pCode", pErrorReport.Code);
		}

		if (!string.IsNullOrEmpty(pErrorReport.ExceptionType))
		{
			whereCmd.Append(" AND exceptionType = @pExceptionType");
			R.AddParameter(cmd, "@pExceptionType", pErrorReport.ExceptionType);
		} 
		
		if (!string.IsNullOrEmpty(pErrorReport.Message))
		{
			whereCmd.Append(" AND message LIKE @pMessage");
			R.AddParameter(cmd, "@pMessage", pErrorReport.Message, ComparisonTypes.LikeFull);
		}

		if (!string.IsNullOrEmpty(pErrorReport.DetailMessage))
		{
			whereCmd.Append(" AND detailMessage LIKE @pDetailMessage");
			R.AddParameter(cmd, "@pDetailMessage", pErrorReport.DetailMessage, ComparisonTypes.LikeFull);
		}
				
		if (pErrorReport.filStartErrorLogDate != null)
		{
			whereCmd.Append (" AND errorLogDate >= @pStartErrorLogDate");
			R.AddInitialDateParameter (cmd, "@pStartErrorLogDate", pErrorReport.filStartErrorLogDate);
		}

		if (pErrorReport.filEndErrorLogDate != null)
		{
			whereCmd.Append (" AND errorLogDate <= @pEndErrorLogDate");
			R.AddFinalDateParameter (cmd, "@pEndErrorLogDate", pErrorReport.filEndErrorLogDate);
		}

		if (!string.IsNullOrEmpty (pErrorReport.filTextPart))
		{
			whereCmd.Append (" AND (message LIKE @pTextPart OR detailmessage LIKE @pTextPart OR stackTrace LIKE @pTextPart)");
			R.AddParameter (cmd, "@pTextPart", pErrorReport.filTextPart, ComparisonTypes.LikeFull);
		}

		R.AddParametersPagging (cmd, pPagInfo);


		mainSQL =
		@"SELECT
			DE.*, GE.errorCount AS errorCount
		FROM
			(SELECT
				applicationName, message, detailMessage, exceptionType, COUNT (1) AS errorCount, max(errorlogdate) AS errorLogdate
			FROM
				Error
			WHERE
				1 = 1 " + whereCmd.ToString () +
			@" GROUP BY
				applicationName, message, detailMessage, exceptionType) GE
			INNER JOIN
				Error DE ON
					DE.applicationname = GE.applicationname
					AND DE.message = GE.message
					AND (DE.detailMessage IS NULL OR DE.detailMessage = GE.detailMessage)
					AND (DE.exceptionType IS NULL OR DE.exceptionType = GE.exceptionType)
					AND DE.errorLogDate = GE.errorLogDate";

		
		temp = "(SELECT count (*) FROM (" + mainSQL + ")  WHERE 1 = 1 " + whereCmd.ToString () + ") RCount ";

		whereCmd.Insert (0, "SELECT *, " + temp + " FROM (" + mainSQL + ") WHERE 1 = 1 ");

		whereCmd.AppendFormat (" ORDER BY {2} DESC LIMIT {0} OFFSET {1}", pPagInfo.PageSize, pPagInfo.PageSize * pPagInfo.PageIndex, pErrorReport.filOrderBy == null ? "errorlogDate" : pErrorReport.filOrderBy);

		cmd.CommandText = whereCmd.ToString ();


		return R.GetBrowse <ErrorReport> (cmd, ConvertToErrorReport, pPagInfo);
	}


	private static ErrorReport ConvertToErrorReport (IDataReader pReader, string pPrefix = "", string pListLoading = "")
	{
		return new ErrorReport ()
		{
			ID = DataHelper.GetNullableInt (pReader, pPrefix + "ID"),
			Code = DataHelper.GetString (pReader, pPrefix + "code"),
			StackTrace = DataHelper.GetString (pReader, pPrefix + "stackTrace"),
			ApplicationName = DataHelper.GetString (pReader, pPrefix + "applicationName"),
			MachineName = DataHelper.GetString (pReader, pPrefix + "machineName"),
			MachineAddress = DataHelper.GetString (pReader, pPrefix + "machineAddress"),
			RemoteMachineAddress = DataHelper.GetString (pReader, pPrefix + "remoteMachineAddress"),
			UserName = DataHelper.GetString (pReader, pPrefix + "userName"),
			DomainName = DataHelper.GetString (pReader, pPrefix + "domainName"),
			OSVersion = DataHelper.GetString (pReader, pPrefix + "OSVersion"),
			RuntimeVersion = DataHelper.GetString (pReader, pPrefix + "runtimeVersion"),
			Message = DataHelper.GetString (pReader, pPrefix + "message"),
			DetailMessage = DataHelper.GetString (pReader, pPrefix + "detailMessage"),
			ErrorLogDate = Convert.ToDateTime (pReader [pPrefix + "errorLogDate"]),
			calErrorCount = DataHelper.GetInt (pReader, pPrefix + "errorCount", 0),
			CustomUserName = DataHelper.GetString (pReader, pPrefix + "customUserName"),
			ProductVersion = DataHelper.GetString(pReader, pPrefix + "productVersion"),
			ExceptionType = DataHelper.GetString (pReader, pPrefix + "exceptionType"),
			WebProxyAddressPort = DataHelper.GetString (pReader, pPrefix + "webProxyAddressPort"),
			calUsersWithSameErrorAndDate = GetUsersWithSameError(DataHelper.GetNullableInt(pReader, pPrefix + "ID"), Convert.ToDateTime(pReader[pPrefix + "errorLogDate"])),
			calUsersWithSameError = GetUsersWithSameError(DataHelper.GetNullableInt(pReader, pPrefix + "ID"), null)
		};
	}


	private static List<string> GetUsersWithSameError (int? pErrorSampleID, DateTime? pErrorLogDate)
	{
		IDbCommand cmd;


		if (pErrorSampleID == null) { return null; }

		cmd = R.CreateCommand (@"SELECT E.customUserName FROM Error E
			INNER JOIN
			(SELECT code, applicationName, message, stackTrace, productVersion, exceptionType, errorLogDate FROM Error WHERE ID = @pID) X
			ON X.code = E.code
			AND X.applicationname = E.applicationname
				AND X.message = E.message
				AND X.stacktrace = E.stacktrace
				AND X.productVersion = E.productVersion
				AND X.exceptionType = E.exceptionType" + 
				(pErrorLogDate == null ? string.Empty : " AND X.errorLogDate = @pErrorLogDate") + 
			" GROUP BY E.customUserName");

		R.AddIDParameter (cmd, "@pID", pErrorSampleID);

		R.AddDateParameter (cmd, "@pErrorLogDate", pErrorLogDate);

		return DataHelper.GetStringVector (R.GetFirstDataTable (cmd));
	}

	#endregion


	#region Escritura

	public static void EmptyLog ()
	{
		IDbCommand cmd;


		cmd = W.CreateCommand ("DELETE FROM Error");

		W.Execute (cmd);
	}


	public static void EmptyLog (string pCode)
	{
		IDbCommand cmd;


		cmd = W.CreateCommand("DELETE FROM Error WHERE code = @pCode");

		W.AddParameter(cmd, "pCode", pCode);

		W.Execute(cmd);
	}


	public static void Delete (params int [] pIDs)
	{
		IDbCommand cmd;


		cmd = W.CreateCommand ("DELETE FROM Error WHERE ID IN (SELECT E.ID FROM Error E INNER JOIN (SELECT code, applicationName, stackTrace, detailMessage FROM Error WHERE ID " + TextHelper.GenerateINIDs (true, pIDs) + ") X ON X.code = E.code AND X.applicationName = E.applicationName AND X.stackTrace = E.stackTrace AND X.detailMessage = E.detailMessage)");

		W.Execute (cmd);
	}


	public static void ClearUntilLastSevenDays ()
	{
		IDbCommand cmd;


		cmd = W.CreateCommand ("DELETE FROM Error WHERE errorLogDate < @pErrorLogDateMin");

		W.AddDateParameter (cmd, "@pErrorLogDateMin", DateTime.Now.AddDays (-7));

		W.Execute (cmd);
	}


	public static void InsertError (ErrorReport pErrorReport)
	{
		IDbCommand cmd;


		cmd = W.CreateCommand (
			@"insert into Error (code, stackTrace, applicationName, machineName, machineAddress, remoteMachineAddress, userName, domainName, OSVersion, runtimeVersion, message, detailMessage, errorLogDate, customUserName, productVersion, exceptionType)
			values (@pCode, @pStackTrace, @pApplicationName, @pMachineName, @pmachineAddress, @pRemoteMachineAddress, @pUserName, @pDomainName, @pOSVersion, @pRuntimeVersion, @pMessage, @pDetailMessage, @pErrorLogDate, @pCustomUserName, @pProductVersion, @pExceptionType)");
			
		W.AddParameter (cmd, "pCode", pErrorReport.Code);
		W.AddParameter (cmd, "pStackTrace", pErrorReport.StackTrace);
		W.AddParameter (cmd, "pApplicationName", pErrorReport.ApplicationName);
		W.AddParameter (cmd, "pMachineName", pErrorReport.MachineName);
		W.AddParameter (cmd, "pmachineAddress", pErrorReport.MachineAddress);
		W.AddParameter (cmd, "pRemotemachineAddress", pErrorReport.RemoteMachineAddress);
		W.AddParameter (cmd, "pUserName", pErrorReport.UserName);
		W.AddParameter (cmd, "pDomainName", pErrorReport.DomainName);
		W.AddParameter (cmd, "pOSVersion", pErrorReport.OSVersion);
		W.AddParameter (cmd, "pRuntimeVersion", pErrorReport.RuntimeVersion);
		W.AddParameter (cmd, "pMessage", pErrorReport.Message);
		W.AddParameter (cmd, "pDetailMessage", pErrorReport.DetailMessage);
		W.AddParameter (cmd, "pErrorLogDate", DateTime.Now);
		W.AddParameter (cmd, "pCustomUserName", pErrorReport.CustomUserName);
		W.AddParameter (cmd, "pProductVersion", pErrorReport.ProductVersion);
		W.AddParameter (cmd, "pExceptionType", pErrorReport.ExceptionType);

		W.Execute (cmd);
	}

	#endregion
}